package com.cn.qtms.dao;//类注释设置模板

import com.cn.qtms.base.dao.BaseDao;
import com.cn.qtms.entity.TmSecGoods;
import com.cn.qtms.vo.SecGoodsVo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Name:TmSecGoodsDao
 * @Description:
 * @Author: zhangyang
 * @Date 2019-12-26 13:24
 **/
@Repository
public interface TmSecGoodsDao extends BaseDao<TmSecGoods,Long> {

    /**
     * 获取某一天所有的秒杀商品信息
     * @param  week 周一到周日
     * @return
     */
    @Query(value = "SELECT sec.id AS id,sec.goods_id AS goods_id,goods.goods_name AS goods_name, " +
            " sec.act_type AS act_type,sec.start_time AS start_time,sec.end_time AS end_time, " +
            " goods.sale_price AS sale_price,sec.sec_price AS sec_price,sec.is_limit AS is_limit, " +
            " sec.limit_no AS limit_no,goods.min_user_level AS min_user_level,sec.each_count AS free_count,sec.each_count AS each_count " +
            "FROM tm_sec_goods sec,tm_sec_act act,tm_goods goods  " +
            "WHERE " +
            " sec.act_id = act.id AND sec.goods_id = goods.id AND act.`status` = 1 AND sec.`status` = 0  " +
            " AND locate( :week,sec.`week` ) > 0  " +
            "AND act.start_date <= date_format( now( ), '%Y-%m-%d' ) AND act.end_date >= date_format( now( ), '%Y-%m-%d' ) " +
            "order by (sec.sec_price/goods.sale_price) asc "
            ,nativeQuery = true)
    List<Map<String,Object>> getSecGoods(@Param("week") String week);

    /**
     * 获取某一天的秒杀商品信息
     * @param week
     * @return
     */
    @Query(value = "SELECT sec.* FROM tm_sec_goods sec,tm_sec_act act,tm_goods goods  " +
            "WHERE " +
            " sec.act_id = act.id AND sec.goods_id = goods.id AND act.`status` = 1 AND sec.`status` = 0  " +
            " AND locate( :week,sec.`week` ) > 0  " +
            "AND act.start_date <= date_format( now( ), '%Y-%m-%d' ) AND act.end_date >= date_format( now( ), '%Y-%m-%d' ) " +
            "order by (sec.sec_price/goods.sale_price) asc "
            ,nativeQuery = true)
    List<TmSecGoods> getTodaysecGoods(@Param("week") String week);

    /**
     * 检查商品是否已经配置过秒杀信息
     * @param actType
     * @param goodsId
     * @param week
     * @return
     */
    @Query(value = "select count(1) from  tm_sec_goods sec,tm_sec_act act,tm_goods goods where sec.act_id=act.id " +
            "and sec.goods_id=goods.id and locate( :week,sec.`week`)>0 and sec.goods_id= :goodsId and sec.act_type= :actType " +
            "and act.start_date>= :startDate and act.end_date<= :endDate and act.`status`=1 and sec.`status`=0 ",nativeQuery = true)
    Integer checkSecGoods(@Param("actType") Integer actType,@Param("goodsId") Long goodsId,@Param("week") String week,@Param("startDate") Date startDate,@Param("endDate") Date endDate);
}
